﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Devart.Data.Oracle;

namespace Fugle
{
    public class FgOracleHelper
    {

        private static OracleDbType ConvertType(System.Data.OracleClient.OracleType OrcType)
        {
            var rtType = OracleDbType.NVarChar;
            switch (OrcType)
            {
                case System.Data.OracleClient.OracleType.BFile:
                    rtType = OracleDbType.BFile;
                    break;
                case System.Data.OracleClient.OracleType.Blob:
                    rtType = OracleDbType.Blob;
                    break;
                case System.Data.OracleClient.OracleType.Byte:
                    rtType = OracleDbType.Byte;
                    break;
                case System.Data.OracleClient.OracleType.Char:
                    rtType = OracleDbType.Char;
                    break;
                case System.Data.OracleClient.OracleType.Clob:
                    rtType = OracleDbType.Clob;
                    break;
                case System.Data.OracleClient.OracleType.Cursor:
                    rtType = OracleDbType.Cursor;
                    break;
                case System.Data.OracleClient.OracleType.DateTime:
                    rtType = OracleDbType.TimeStamp;
                    break;
                case System.Data.OracleClient.OracleType.Double:
                    rtType = OracleDbType.Double;
                    break;
                case System.Data.OracleClient.OracleType.Float:
                    rtType = OracleDbType.Float;
                    break;
                case System.Data.OracleClient.OracleType.Int16:
                    rtType = OracleDbType.Int16;
                    break;
                case System.Data.OracleClient.OracleType.Int32:
                    rtType = OracleDbType.Integer;
                    break;
                case System.Data.OracleClient.OracleType.IntervalDayToSecond:
                    rtType = OracleDbType.IntervalDS;
                    break;
                case System.Data.OracleClient.OracleType.IntervalYearToMonth:
                    rtType = OracleDbType.IntervalYM;
                    break;
                case System.Data.OracleClient.OracleType.LongRaw:
                    rtType = OracleDbType.LongRaw;
                    break;
                case System.Data.OracleClient.OracleType.LongVarChar:
                    rtType = OracleDbType.NChar;
                    break;
                case System.Data.OracleClient.OracleType.NChar:
                    rtType = OracleDbType.NChar;
                    break;
                case System.Data.OracleClient.OracleType.NClob:
                    rtType = OracleDbType.NClob;
                    break;
                case System.Data.OracleClient.OracleType.Number:
                    rtType = OracleDbType.Number;
                    break;
                case System.Data.OracleClient.OracleType.NVarChar:
                    rtType = OracleDbType.NVarChar;
                    break;
                case System.Data.OracleClient.OracleType.Raw:
                    rtType = OracleDbType.Raw;
                    break;
                case System.Data.OracleClient.OracleType.RowId:
                    rtType = OracleDbType.RowId;
                    break;
                case System.Data.OracleClient.OracleType.SByte:
                    rtType = OracleDbType.Byte;
                    break;
                case System.Data.OracleClient.OracleType.Timestamp:
                    rtType = OracleDbType.TimeStamp;
                    break;
                case System.Data.OracleClient.OracleType.TimestampLocal:
                    rtType = OracleDbType.TimeStampLTZ;
                    break;
                case System.Data.OracleClient.OracleType.TimestampWithTZ:
                    rtType = OracleDbType.TimeStampTZ;
                    break;
                case System.Data.OracleClient.OracleType.UInt16:
                    rtType = OracleDbType.Int16;
                    break;
                case System.Data.OracleClient.OracleType.UInt32:
                    rtType = OracleDbType.Int64;
                    break;
                case System.Data.OracleClient.OracleType.VarChar:
                    rtType = OracleDbType.VarChar;
                    break;
            }
            return rtType;
        }
        private static OracleParameter[] ConvertTypeToParameters(List<IDataParameter> ListParameters)
        {
            var rtparameters = new List<OracleParameter>();
            foreach (var par in ListParameters)
            {
                var pap = new OracleParameter()
                {
                    ParameterName = par.ParameterName,
                    Value = par.Value,
                    Direction = par.Direction,
                    DbType = par.DbType,
                    OracleDbType = ConvertType((par as System.Data.OracleClient.OracleParameter).OracleType)
                };
                if (pap.OracleDbType == OracleDbType.VarChar && pap.Size >= 2000)
                    pap.OracleDbType = OracleDbType.Clob;
                rtparameters.Add(pap);
            }
            return rtparameters.ToArray();
        }
        public static object ExecuteScalar(string connStr, string commandText, CommandType commandType, ref string errStr, List<IDataParameter> param, int outTime = 30)
        {
            return ExecuteScalar(connStr, commandText, commandType, ref errStr, ConvertTypeToParameters(param), outTime);
        }
        public static object ExecuteScalar(string connStr, string commandText, CommandType commandType, ref string errStr, OracleParameter[] param, int outTime = 30)
        {
            commandText = ParameterSplitFunc.Anasysis(commandText, param, commandType);
            object ret = DBNull.Value;
            errStr = string.Empty;
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                OracleConnectionStringBuilder osb=new OracleConnectionStringBuilder();
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        //cmd.CommandTimeout = outTime;
                        cmd.CommandType = commandType;
                        cmd.PassParametersByName = true;
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    { errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText; }
                }
                conn.Close();
            }
            return ret;
        }

        public static int ExecuteNonQuery(string connStr, string commandText, CommandType commandType, ref string errStr, List<IDataParameter> param, int outTime = 30)
        {
            return ExecuteNonQuery(connStr, commandText, commandType, ref errStr, ConvertTypeToParameters(param), outTime);
        }
        public static int ExecuteNonQuery(string connStr, string commandText, CommandType commandType, ref string errStr, OracleParameter[] param, int outTime = 30)
        {
            int ret = -1;
            errStr = string.Empty;
            commandText = ParameterSplitFunc.Anasysis(commandText, param, commandType);
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        ret = 1;
                        //cmd.CommandTimeout = outTime;
                        cmd.CommandType = commandType;
                        cmd.PassParametersByName = true;
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        if (commandType == CommandType.Text)
                            cmd.ExecuteNonQuery();
                        else
                            cmd.ExecuteNonQuery();
                        conn.Close();
                        return ret;
                    }
                    catch (Exception ex)
                    {
                        ret = -1;
                        errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText;
                    }
                }

            }
            return ret;
        }

        public static int ExecuteNonQuery2(string connStr, string commandText, CommandType commandType, ref string errStr, List<IDataParameter> param, int outTime = 30)
        {
            return ExecuteNonQuery2(connStr, commandText, commandType, ref errStr, ConvertTypeToParameters(param), outTime);
        }
        public static int ExecuteNonQuery2(string connStr, string commandText, CommandType commandType, ref string errStr, OracleParameter[] param, int outTime = 60)
        {
            int ret = -1;
            errStr = string.Empty;
            commandText = ParameterSplitFunc.Anasysis(commandText, param, commandType);
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        cmd.CommandTimeout = outTime;
                        cmd.CommandType = commandType;
                        cmd.PassParametersByName = true;
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        if (commandType == CommandType.Text)
                            ret = cmd.ExecuteNonQuery();
                        else
                            ret = cmd.ExecuteNonQuery();
                        conn.Close();
                        return ret;
                    }
                    catch (Exception ex)
                    {
                        ret = -1;
                        errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText;
                    }
                }

            }
            return ret;
        }

        public static int ExecuteNonQueryTransaction(string connStr, List<string> commandText, CommandType commandType, ref string errStr, List<List<IDataParameter>> param, int outTime = 30)
        {
            var ListParameters = new List<OracleParameter[]>();
            foreach (var Parameter in param)
                ListParameters.Add(ConvertTypeToParameters(Parameter));
            return ExecuteNonQueryTransaction(connStr, commandText, commandType, ref errStr, ListParameters, outTime);
        }
        public static int ExecuteNonQueryTransaction(string connStr, List<string> commandText, CommandType commandType, ref string errStr, List<OracleParameter[]> param, int outTime = 30)
        {
            int ret = -1;
            var SqlTemp = string.Empty;
            errStr = string.Empty;
            if (param.Count != 0 && commandText.Count != param.Count)
            {
                errStr = "Message：" + "语句和参数条数不匹配";
                return -1;
            }
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        ret = 1;
                        for (int i = 0; i < commandText.Count; i++)
                        {
                            SqlTemp = commandText[i];
                            if (param != null && param.Count > 0 && param[i].Length > 0)
                                SqlTemp = ParameterSplitFunc.Anasysis(SqlTemp, param[i], commandType);
                            using (OracleCommand cmd = new OracleCommand(SqlTemp, conn))
                            {
                                //cmd.CommandTimeout = outTime;
                                cmd.CommandType = commandType;
                                cmd.PassParametersByName = true;
                                if (param != null && param.Count > 0 && param[i].Length > 0)
                                    cmd.Parameters.AddRange(param[i]);
                                cmd.Transaction = tran;
                                //ret += cmd.ExecuteNonQuery();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ret = -1;
                        tran.Rollback();
                        errStr = "Message：" + ex.Message + "\r\t";
                    }
                    if (string.IsNullOrEmpty(errStr))
                        tran.Commit();
                }
                conn.Close();
            }
            return ret;
        }

        public static DataSet Query(string connStr, string commandText, CommandType commandType, ref string errStr, List<IDataParameter> param, int outTime = 30)
        {
            return Query(connStr, commandText, commandType, ref errStr, ConvertTypeToParameters(param), outTime);
        }

        private static DataSet Query(string connStr, string commandText, CommandType commandType, ref string errStr, OracleParameter[] param, int outTime = 30)
        {
            DataSet rtds = new DataSet();
            errStr = string.Empty;
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleTransaction tran = conn.BeginTransaction())
                {
                    using (OracleCommand cmd = new OracleCommand(commandText, conn))
                    {
                        try
                        {
                            //cmd.CommandTimeout = outTime;
                            cmd.CommandType = commandType;
                            cmd.Transaction = tran;
                            cmd.PassParametersByName = true;
                            if (param != null && param.Length > 0)
                            {
                                if (commandType == CommandType.StoredProcedure)
                                {
                                    //var _ParametersCount = ParametersCount(conn,tran, commandText, ref errStr);
                                    cmd.ParameterCheck = false;
                                    var parameterList = ParametersList(conn, tran, commandText, ref errStr);
                                    if (!string.IsNullOrEmpty(errStr)) throw new Exception(errStr);
                                    var pSize = parameterList.Count;
                                    for (int i = 0; i < pSize; i++)
                                    {
                                        if (parameterList[i].ParameterDirection == OracleProcParameterDirection.Out)
                                        {
                                            cmd.Parameters.Add(parameterList[i].ArguName, OracleDbType.Cursor,ParameterDirection.Output);
                                        }
                                        else
                                        {
                                            param[i].ParameterName = parameterList[i].ArguName;
                                            cmd.Parameters.Add(param[i]);
                                        }
                                       
                                    }
                                    //_ParametersCount--;
                                    //if (_ParametersCount > 0)
                                    //{
                                    //    for (int i = param.Length - 2; i >= 0; i--)
                                    //    {
                                    //        if (param[i].Direction == ParameterDirection.Input && _ParametersCount > 0)
                                    //        {
                                    //            param[i].Direction = ParameterDirection.Output;
                                    //            param[i].OracleDbType = OracleDbType.RefCursor;
                                    //            _ParametersCount--;
                                    //        }
                                    //    }
                                    //}
                                }
                                else
                                {
                                    var newSql = ParameterSplitFunc.Anasysis(commandText, param,CommandType.Text);
                                    cmd.CommandText = newSql;
                                    cmd.Parameters.AddRange(param);
                                }
                            }
                            using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                                da.Fill(rtds);
                        }

                        catch (Exception ex)
                        {
                            tran.Rollback();
                            errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText;
                        }
                        if (string.IsNullOrEmpty(errStr))
                            tran.Commit();
                    }
                }
                conn.Close();
            }
            return rtds;
        }

        private static int ParametersCount(OracleConnection conn, OracleTransaction tran, string commandText, ref string errStr)
        {
            int ret = 0;
            errStr = string.Empty;
            using (OracleCommand cmd = new OracleCommand("SELECT COUNT(*) FROM USER_ARGUMENTS WHERE lower(OBJECT_NAME)=lower('" + commandText + "') AND IN_OUT='OUT'", conn))
            {
                try
                {
                    //cmd.CommandTimeout = 30;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = tran;
                    var Scalar = cmd.ExecuteScalar();
                    ret = Scalar == null ? 0 : Convert.ToInt32(Scalar);
                }
                catch (Exception ex)
                { errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText; }
            }
            return ret;
        }


        private static List<OracleProcParameter> ParametersList(OracleConnection conn, OracleTransaction tran, string commandText, ref string errStr)
        {
            errStr = string.Empty;
            var list = new List<OracleProcParameter>();
            list = FgParameterCache.GetParameters(conn.UserId, commandText);
            if (list != null) return list;
            list = new List<OracleProcParameter>();
            //using (OracleCommand cmd = new OracleCommand("SELECT argument_name,position,in_out FROM FGHIS5.USER_ARGUMENTS_VIEW WHERE OWNER='"+conn.UserId.ToUpper()+"' and  OBJECT_NAME='" + commandText.ToUpper() + "' order by position", conn))
            using (OracleCommand cmd = new OracleCommand("SELECT argument_name,position,in_out FROM USER_ARGUMENTS WHERE OBJECT_NAME='" + commandText.ToUpper() + "' order by position", conn))
            {
                try
                {
                    //cmd.CommandTimeout = 30;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = tran;
                    DataTable dt = new DataTable();
                    using (OracleDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                    }
                    foreach (DataRow row in dt.Rows)
                    {
                        list.Add(new OracleProcParameter
                        {
                            ArguName = row[0].ToString(),
                            Position = int.Parse(row[1].ToString()),
                            ParameterDirection = row[2].ToString() == "OUT" ? OracleProcParameterDirection.Out : OracleProcParameterDirection.In
                        });
                    }
                }
                catch (Exception ex)
                { errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText; }
            }
            FgParameterCache.AddProcParameterCache(conn.UserId,commandText,list);
            return list;
        }

        public static IDataReader ExecuteReader(string connStr, string commandText, CommandType commandType, ref string errStr, List<IDataParameter> param, int outTime = 30)
        {
            return ExecuteReader(connStr, commandText, commandType, ref errStr, ConvertTypeToParameters(param), outTime);
        }
        public static IDataReader ExecuteReader(string connStr, string commandText, CommandType commandType, ref string errStr, OracleParameter[] param, int outTime = 30)
        {
            IDataReader ret = null;
            errStr = string.Empty;
            commandText = ParameterSplitFunc.Anasysis(commandText, param, commandType);
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        //cmd.CommandTimeout = outTime;
                        cmd.CommandType = commandType;
                        cmd.PassParametersByName = true;
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        ret = cmd.ExecuteReader();
                    }
                    catch (Exception ex)
                    { errStr = "Message：" + ex.Message + "\r\t" + "SQL：" + commandText; }
                }
                conn.Close();
            }
            return ret;
        }

        public static bool ExecSqlList(ref List<SqlEntity> _pList, string connStr)
        {
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleTransaction tran = conn.BeginTransaction())
                {
                    for (int j = 0; j < _pList.Count; j++)
                    {
                        using (OracleCommand cmd = new OracleCommand(_pList[j].sql, conn))
                        {
                            try
                            {
                                cmd.Transaction = tran;
                                cmd.PassParametersByName = true;
                                if (_pList[j].Paras != null && _pList[j].Paras.Count > 0)
                                {
                                    var param = ConvertTypeToParameters(_pList[j].Paras);
                                    var newSql = ParameterSplitFunc.Anasysis(_pList[j].sql, param, CommandType.Text);
                                    cmd.CommandText = newSql;
                                    cmd.Parameters.AddRange(param);
                                }
                                _pList[j].count= cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();
                                throw ex;
                            }
                        }
                    }
                    tran.Commit();
                }
                conn.Close();
            }
            return true;
        }




        public static int ExcuteBulkData(string connStr, DataTable table, string tableName, ref string errStr, int outTime = 30)
        {
            int ret = 0;
            errStr = string.Empty;
            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentException("必须指定批量插入的表名称", "tableName");

            var columnRowData = GetDictionary(table);

            if (columnRowData == null || columnRowData.Count < 1)
                throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");

            string[] dbColumns = columnRowData.Keys.ToArray();
            StringBuilder sbCmdText = new StringBuilder();
            if (columnRowData.Count > 0)
            {
                //准备插入的SQL  
                sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
                sbCmdText.Append(string.Join(",", dbColumns));
                sbCmdText.Append(") VALUES (");
                sbCmdText.Append(":" + string.Join(",:", dbColumns));
                sbCmdText.Append(")");

                using (OracleConnection conn = new OracleConnection(connStr))
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        //绑定批处理的行数  
                        cmd.ExecuteArray(table.Rows.Count) ;
                        cmd.PassParametersByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sbCmdText.ToString();
                        //cmd.CommandTimeout = outTime;

                        //创建参数  
                        OracleParameter oraParam;
                        List<IDbDataParameter> cacher = new List<IDbDataParameter>();
                        //OracleDbType dbType = OracleDbType.Varchar2;
                        foreach (string colName in dbColumns)
                        {
                            //dbType = GetOracleDbType(columnRowData[colName]);
                            oraParam = new OracleParameter();
                            oraParam.ParameterName = colName;
                            oraParam.Direction = ParameterDirection.Input;
                            //oraParam = dbType;

                            oraParam.Value = columnRowData[colName];
                            cmd.Parameters.Add(oraParam);
                        }
                        //打开连接  
                        conn.Open();

                        /*执行批处理*/
                        var trans = conn.BeginTransaction();
                        try
                        {
                            cmd.Transaction = trans;
                            ret = cmd.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            if (conn != null) conn.Close();
                        }
                    }
                }
            }
            return ret;
        }

        private static Dictionary<string, object> GetDictionary(DataTable dt)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            object[,] obj = new object[dt.Columns.Count, dt.Rows.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                var _type = dt.Columns[i].DataType;
                if (_type == typeof(string) || _type == typeof(Guid))
                {
                    var obj2 = new string[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (string)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(DateTime))
                {
                    var obj2 = new DateTime[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (DateTime)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(int) || _type == typeof(short))
                {
                    var obj2 = new int[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (int)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(long))
                {
                    var obj2 = new long[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (long)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(decimal))
                {
                    var obj2 = new decimal[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (decimal)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(double))
                {
                    var obj2 = new double[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (double)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(float))
                {
                    var obj2 = new float[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (float)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(bool) || _type == typeof(Boolean))
                {
                    var obj2 = new bool[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (bool)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(byte))
                {
                    var obj2 = new byte[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (byte)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }
                else if (_type == typeof(char))
                {
                    var obj2 = new char[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j][i] != null && dt.Rows[j][i] != DBNull.Value)
                            obj2[j] = (char)dt.Rows[j][i];
                    }
                    dic.Add(dt.Columns[i].Caption, obj2);
                }

            }
            return dic;
        }

        //private static OracleDbType GetOracleDbType(object value)
        //{
        //    OracleDbType dataType = OracleDbType.Varchar2;
        //    if (value is string[])
        //    {
        //        dataType = OracleDbType.Varchar2;
        //    }
        //    else if (value is DateTime[])
        //    {
        //        dataType = OracleDbType.TimeStamp;
        //    }
        //    else if (value is int[] || value is short[])
        //    {
        //        dataType = OracleDbType.Int32;
        //    }
        //    else if (value is long[])
        //    {
        //        dataType = OracleDbType.Int64;
        //    }
        //    else if (value is decimal[] || value is double[] || value is float[])
        //    {
        //        dataType = OracleDbType.Decimal;
        //    }
        //    else if (value is Guid[])
        //    {
        //        dataType = OracleDbType.Varchar2;
        //    }
        //    else if (value is bool[] || value is Boolean[])
        //    {
        //        dataType = OracleDbType.Byte;
        //    }
        //    else if (value is byte[])
        //    {
        //        dataType = OracleDbType.Blob;
        //    }
        //    else if (value is char[])
        //    {
        //        dataType = OracleDbType.Char;
        //    }
        //    return dataType;
        //}
    }
}
